﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;

namespace APSIM
{
    class ExcelToDataset
    {
        string ExcelPath = "";
        public ExcelToDataset(string ExcelPath)

        {
            this.ExcelPath = ExcelPath;
        }

        public ExcelToDataset()
        {
        }

        // 在DataGridView中显示的数据
        public DataSet ExcelToDataGridView(string ExcelPath)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0'";
            DataSet ds = new DataSet();
            OleDbDataAdapter oada = new OleDbDataAdapter("select site,date,radn,maxt,mint,rain,evap from [Sheet1$]", strConn);
            oada.Fill(ds);
            return ds;
        }

        //从Excel中读取气象数据
        //输入（文件路径），返回（DataSet），默认工作表名称为Sheet1
        public DataSet ExcelToDataSet(string ExcelPath)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0'";
            DataSet ds = new DataSet();
            OleDbDataAdapter oada = new OleDbDataAdapter("select year(date) as iyear,month(date) as imonth,round(avg(maxt),2) as maxtmean,round(avg(mint),2) as mintmean from [Sheet1$] group by year(date),month(date)", strConn);
            oada.Fill(ds);
            return ds;
        }

        //annual average ambient temperature
        // 每月气温均值累加和/月数
        public double GetAnnualTemAverage(string ExcelPath)
        {
            DataSet ds = this.ExcelToDataSet(ExcelPath);
            
            //月份个数
            int monthcount = ds.Tables[0].Rows.Count;
            //月均值
            double tmean = 0;
            //月均值累加和
            double sum = 0;
            //年平均气温
            double TAV = 0;
            for (int i = 0; i < monthcount; i++)
            {
                double maxt = Convert.ToDouble(ds.Tables[0].Rows[i][2]);
                double mint = Convert.ToDouble(ds.Tables[0].Rows[i][3]);
                tmean = (maxt + mint) / 2;
                sum += tmean;
            }
            TAV = sum / monthcount;
            return TAV;
        }

        //annual amplitude in mean monthly temperature
        //
        public double GetAnnualTemAmplitude(string ExcelPath)
        {
            DataSet ds = this.ExcelToDataSet(ExcelPath);

            int monthcount = ds.Tables[0].Rows.Count;
            double[] tmeann = new double[12];
            double sum = 0;
            double AMP = 0;
            for (int i = 0; i < monthcount / 12; i++)
            {
                //年
                for (int j = 1; j < 13; j++)
                {
                    //月
                    int c = i * 12 + j;
                    double maxt = Convert.ToDouble(ds.Tables[0].Rows[c-1][2]);
                    double mint = Convert.ToDouble(ds.Tables[0].Rows[c-1][3]);
                    tmeann[j - 1] = (maxt + mint) / 2;
                }
                //每年12个月气温月均温是的最大值减最小值
                double range = tmeann.Max() - tmeann.Min();
                sum += range;
            }
            AMP = sum / (monthcount / 12);
            return AMP;
        }


        //从Met文件（Excel格式）
//        public DataTable MetToExcel(string ExcelPath)
        public DataSet MetToExcel(string ExcelPath)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + ExcelPath + ";Extended Properties='Excel 8.0'";
            DataSet ds = new DataSet();
            OleDbDataAdapter oada = new OleDbDataAdapter("select site,date,year(date) as iyear,radn,maxt,mint,rain,evap from [Sheet1$]", strConn);
            oada.Fill(ds);

            DataColumn newcolumn = new DataColumn();
            newcolumn.DataType = System.Type.GetType("System.Int16");
            newcolumn.ColumnName = "day";
            ds.Tables[0].Columns.Add(newcolumn);

            int count = ds.Tables[0].Rows.Count;
            int[] dayofyear = new int[count];
            for (int i = 0; i < count; i++)
            {
                DateTime dt = Convert.ToDateTime(ds.Tables[0].Rows[i][1]);
                ds.Tables[0].Rows[i]["day"] = dt.DayOfYear;
            }

            ds.Tables[0].Columns["site"].SetOrdinal(0);
            ds.Tables[0].Columns["iyear"].SetOrdinal(1);
            ds.Tables[0].Columns["day"].SetOrdinal(2);
            ds.Tables[0].Columns["radn"].SetOrdinal(3);
            ds.Tables[0].Columns["maxt"].SetOrdinal(4);
            ds.Tables[0].Columns["mint"].SetOrdinal(5);
            ds.Tables[0].Columns["rain"].SetOrdinal(6);
            ds.Tables[0].Columns["evap"].SetOrdinal(7);
            ds.Tables[0].Columns["date"].SetOrdinal(8);


            //删除不需要的列
            ds.Tables[0].Columns.RemoveAt(8);
            //return ds.Tables[0];
            return ds;
        }
    }
}